{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "60c5d6b1",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            销售日期    员工工号  销售员       货号                    销售单编号  销量    销售额\n",
      "0     2017-01-01  SS0141  钱多多  STY0026  C001S001-2017-01-01-001   1  561.2\n",
      "1     2017-01-01  SS0039  金花花  STY0047  C001S001-2017-01-01-002   1  270.1\n",
      "2     2017-01-01  SS0035  胡大花  STY0002  C001S001-2017-01-01-003   1  322.6\n",
      "3     2017-01-01  SS0035  胡大花  STY0016  C001S001-2017-01-01-003   1  222.7\n",
      "4     2017-01-01  SS0035  胡大花  STY0041  C001S001-2017-01-01-003   1  608.5\n",
      "...          ...     ...  ...      ...                      ...  ..    ...\n",
      "14277 2017-12-31  SS0035  胡大花  STY1019  C001S001-2017-12-31-045   1  947.0\n",
      "14278 2017-12-31  SS0122   赵里  STY0965  C001S001-2017-12-31-046   1  608.5\n",
      "14279 2017-12-31  SS0035  胡大花  STY1051  C001S001-2017-12-31-051   1  134.7\n",
      "14280 2017-12-31  SS0035  胡大花  STY1047  C001S001-2017-12-31-051   1  743.9\n",
      "14281 2017-12-31  SS0035  胡大花  STY1055  C001S001-2017-12-31-051   1  270.1\n",
      "\n",
      "[14282 rows x 7 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "file_path = r\"C:\\Users\\Draw the sword\\Desktop\\商业数据分析\\《Power BI商业数据分析项目实战》\\第3篇 销售案例6 7 8 9\\第6章\\数据源.xlsx\"\n",
    "df = pd.read_excel(file_path, sheet_name='销售明细', engine='openpyxl')\n",
    "df_1 = pd.read_excel(file_path, sheet_name='销售目标', engine='openpyxl')\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "586dc693",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          销售日期      销售额\n",
      "0   2017-01-01  31204.1\n",
      "1   2017-01-02  18385.2\n",
      "2   2017-01-03   8132.6\n",
      "3   2017-01-04   1870.2\n",
      "4   2017-01-05   4821.4\n",
      "..         ...      ...\n",
      "360 2017-12-27   8323.8\n",
      "361 2017-12-28   8072.9\n",
      "362 2017-12-29   8448.5\n",
      "363 2017-12-30  24115.4\n",
      "364 2017-12-31  33065.8\n",
      "\n",
      "[365 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "销售汇总 = df.groupby('销售日期')['销售额'].sum().reset_index()\n",
    "print(销售汇总)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "5d60ca69",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售日期</th>\n",
       "      <th>员工工号</th>\n",
       "      <th>销售员</th>\n",
       "      <th>货号</th>\n",
       "      <th>销售单编号</th>\n",
       "      <th>销量</th>\n",
       "      <th>销售额</th>\n",
       "      <th>月份</th>\n",
       "      <th>星期</th>\n",
       "      <th>月份&amp;星期</th>\n",
       "      <th>X月星期X平均销售</th>\n",
       "      <th>X月星期X系数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>SS0141</td>\n",
       "      <td>钱多多</td>\n",
       "      <td>STY0026</td>\n",
       "      <td>C001S001-2017-01-01-001</td>\n",
       "      <td>1</td>\n",
       "      <td>561.2</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "      <td>384.113369</td>\n",
       "      <td>1.583302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>SS0039</td>\n",
       "      <td>金花花</td>\n",
       "      <td>STY0047</td>\n",
       "      <td>C001S001-2017-01-01-002</td>\n",
       "      <td>1</td>\n",
       "      <td>270.1</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "      <td>384.113369</td>\n",
       "      <td>1.583302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>SS0035</td>\n",
       "      <td>胡大花</td>\n",
       "      <td>STY0002</td>\n",
       "      <td>C001S001-2017-01-01-003</td>\n",
       "      <td>1</td>\n",
       "      <td>322.6</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "      <td>384.113369</td>\n",
       "      <td>1.583302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>SS0035</td>\n",
       "      <td>胡大花</td>\n",
       "      <td>STY0016</td>\n",
       "      <td>C001S001-2017-01-01-003</td>\n",
       "      <td>1</td>\n",
       "      <td>222.7</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "      <td>384.113369</td>\n",
       "      <td>1.583302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>SS0035</td>\n",
       "      <td>胡大花</td>\n",
       "      <td>STY0041</td>\n",
       "      <td>C001S001-2017-01-01-003</td>\n",
       "      <td>1</td>\n",
       "      <td>608.5</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "      <td>384.113369</td>\n",
       "      <td>1.583302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14277</th>\n",
       "      <td>2017-12-28</td>\n",
       "      <td>SS0122</td>\n",
       "      <td>赵里</td>\n",
       "      <td>STY1075</td>\n",
       "      <td>C001S001-2017-12-28-017</td>\n",
       "      <td>1</td>\n",
       "      <td>276.2</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>124</td>\n",
       "      <td>489.585714</td>\n",
       "      <td>1.456677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14278</th>\n",
       "      <td>2017-12-28</td>\n",
       "      <td>SS0149</td>\n",
       "      <td>完颜朵</td>\n",
       "      <td>STY1010</td>\n",
       "      <td>C001S001-2017-12-28-018</td>\n",
       "      <td>1</td>\n",
       "      <td>414.7</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>124</td>\n",
       "      <td>489.585714</td>\n",
       "      <td>1.456677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14279</th>\n",
       "      <td>2017-12-28</td>\n",
       "      <td>SS0041</td>\n",
       "      <td>王浩然</td>\n",
       "      <td>STY0978</td>\n",
       "      <td>C001S001-2017-12-28-018</td>\n",
       "      <td>1</td>\n",
       "      <td>784.2</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>124</td>\n",
       "      <td>489.585714</td>\n",
       "      <td>1.456677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14280</th>\n",
       "      <td>2017-12-28</td>\n",
       "      <td>SS0149</td>\n",
       "      <td>完颜朵</td>\n",
       "      <td>STY0851</td>\n",
       "      <td>C001S001-2017-12-28-019</td>\n",
       "      <td>1</td>\n",
       "      <td>553.2</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>124</td>\n",
       "      <td>489.585714</td>\n",
       "      <td>1.456677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14281</th>\n",
       "      <td>2017-12-28</td>\n",
       "      <td>SS0149</td>\n",
       "      <td>完颜朵</td>\n",
       "      <td>STY0796</td>\n",
       "      <td>C001S001-2017-12-28-019</td>\n",
       "      <td>1</td>\n",
       "      <td>255.5</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>124</td>\n",
       "      <td>489.585714</td>\n",
       "      <td>1.456677</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14282 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            销售日期    员工工号  销售员       货号                    销售单编号  销量    销售额  \\\n",
       "0     2017-01-01  SS0141  钱多多  STY0026  C001S001-2017-01-01-001   1  561.2   \n",
       "1     2017-01-01  SS0039  金花花  STY0047  C001S001-2017-01-01-002   1  270.1   \n",
       "2     2017-01-01  SS0035  胡大花  STY0002  C001S001-2017-01-01-003   1  322.6   \n",
       "3     2017-01-01  SS0035  胡大花  STY0016  C001S001-2017-01-01-003   1  222.7   \n",
       "4     2017-01-01  SS0035  胡大花  STY0041  C001S001-2017-01-01-003   1  608.5   \n",
       "...          ...     ...  ...      ...                      ...  ..    ...   \n",
       "14277 2017-12-28  SS0122   赵里  STY1075  C001S001-2017-12-28-017   1  276.2   \n",
       "14278 2017-12-28  SS0149  完颜朵  STY1010  C001S001-2017-12-28-018   1  414.7   \n",
       "14279 2017-12-28  SS0041  王浩然  STY0978  C001S001-2017-12-28-018   1  784.2   \n",
       "14280 2017-12-28  SS0149  完颜朵  STY0851  C001S001-2017-12-28-019   1  553.2   \n",
       "14281 2017-12-28  SS0149  完颜朵  STY0796  C001S001-2017-12-28-019   1  255.5   \n",
       "\n",
       "       月份  星期 月份&星期   X月星期X平均销售   X月星期X系数  \n",
       "0       1   7    17  384.113369  1.583302  \n",
       "1       1   7    17  384.113369  1.583302  \n",
       "2       1   7    17  384.113369  1.583302  \n",
       "3       1   7    17  384.113369  1.583302  \n",
       "4       1   7    17  384.113369  1.583302  \n",
       "...    ..  ..   ...         ...       ...  \n",
       "14277  12   4   124  489.585714  1.456677  \n",
       "14278  12   4   124  489.585714  1.456677  \n",
       "14279  12   4   124  489.585714  1.456677  \n",
       "14280  12   4   124  489.585714  1.456677  \n",
       "14281  12   4   124  489.585714  1.456677  \n",
       "\n",
       "[14282 rows x 12 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['月份'] = pd.DatetimeIndex(df['销售日期']).month\n",
    "df['星期'] = pd.DatetimeIndex(df['销售日期']).dayofweek + 1\n",
    "df['月份&星期'] = df['月份'].astype(str) + df['星期'].astype(str)\n",
    "\n",
    "def calculate_average_sales(group):\n",
    "    return group['销售额'].sum() / len(group['星期'])\n",
    "\n",
    "average_sales = df.groupby('月份&星期').apply(calculate_average_sales).reset_index(name='X月星期X平均销售')\n",
    "\n",
    "def calculate_coefficient(row):\n",
    "    month = row['月份']\n",
    "    min_avg_sales_for_month = df[(df['月份'] == month)]['X月星期X平均销售'].min()\n",
    "    if min_avg_sales_for_month == 0:\n",
    "        return 0\n",
    "    return row['X月星期X平均销售'] / min_avg_sales_for_month\n",
    "\n",
    "df = df.merge(average_sales, on='月份&星期')\n",
    "\n",
    "df['X月星期X系数'] = df.apply(calculate_coefficient, axis=1)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "485000cb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            日期  月份  星期 月份&星期\n",
      "0   2018-01-01   1   1    11\n",
      "1   2018-01-02   1   2    12\n",
      "2   2018-01-03   1   3    13\n",
      "3   2018-01-04   1   4    14\n",
      "4   2018-01-05   1   5    15\n",
      "..         ...  ..  ..   ...\n",
      "360 2018-12-27  12   4   124\n",
      "361 2018-12-28  12   5   125\n",
      "362 2018-12-29  12   6   126\n",
      "363 2018-12-30  12   7   127\n",
      "364 2018-12-31  12   1   121\n",
      "\n",
      "[365 rows x 4 columns]\n"
     ]
    }
   ],
   "source": [
    "# 创建2018年的日期范围\n",
    "sales_target_2018 = pd.date_range(start='2018-01-01', end='2018-12-31')\n",
    "\n",
    "# 创建一个DataFrame\n",
    "df = pd.DataFrame(sales_target_2018, columns=['日期'])\n",
    "\n",
    "# 计算月份\n",
    "df['月份'] = df['日期'].dt.month\n",
    "\n",
    "# 计算星期（2表示周一为1，周日为7）\n",
    "df['星期'] = df['日期'].dt.weekday + 1\n",
    "\n",
    "# 合并月份和星期\n",
    "df['月份&星期'] = df['月份'].astype(str) + df['星期'].astype(str)\n",
    "\n",
    "# 打印结果\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "73280067",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4269208.9\n"
     ]
    }
   ],
   "source": [
    "def calculate_last_month_sales(df):\n",
    "    # 获取当前月份的上一个月\n",
    "    last_month = df['销售日期'].dt.to_period('M') - 1\n",
    "    # 将上个月份转换回日期\n",
    "    last_month_dates = last_month.apply(lambda x: x.start_time)\n",
    "    # 过滤出上个月的数据\n",
    "    last_month_df = df[df['销售日期'].dt.to_period('M').isin(last_month)]\n",
    "    # 计算上个月的销售业绩总和\n",
    "    last_month_sales = last_month_df['销售额'].sum()\n",
    "    return last_month_sales\n",
    "\n",
    "# 调用函数计算上月业绩\n",
    "上月业绩 = calculate_last_month_sales(df)\n",
    "print(上月业绩)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
